USE [Test_GCCS]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetPrinciplesSummary]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetPrinciplesSummary]
GO

CREATE PROCEDURE [dbo].[spGetPrinciplesSummary]
AS
	SET NOCOUNT ON;

	DECLARE @Principles TABLE
	(
		ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		PrincipleTemplateId INT NOT NULL,
		PrincipleGccsName NVARCHAR(50) NOT NULL,
		PrincipleGirName NVARCHAR(50) NOT NULL,
		PrincipleGccsDescription NVARCHAR(1500) NOT NULL,
		PrincipleGirDescription NVARCHAR(1500) NOT NULL,
		PillarId INT NOT NULL,
		PillarName NVARCHAR(100) NOT NULL,
		Issue NVARCHAR(max) NULL,
		PrincipleAssessment INT NOT NULL,
		PrincipleAssessmentEffectiveSince DATETIME NOT NULL,
		AssessmentSummaryAndConclusionIntroductionSentence NVARCHAR(300) NOT NULL,
		PrincipleId UNIQUEIDENTIFIER NOT NULL,
		AssessmentComment1 NVARCHAR(1200) NULL,
		AssessmentComment2 NVARCHAR(1200) NULL,
		AssessmentComment3 NVARCHAR(1200) NULL,
		AssessmentComment4 NVARCHAR(1200) NULL,
		AssessmentComment5 NVARCHAR(1200) NULL,
		AssessmentComment6 NVARCHAR(1200) NULL
	)
	INSERT @Principles
	SELECT	dbo.GccsCompanyProfiles.CompanyId,
			dbo.GccsPrincipleTemplates.Id AS PrincipleTemplateId, 
            dbo.GccsPrincipleTemplates.GccsName AS PrincipleGccsName, 
			dbo.GccsPrincipleTemplates.GirName AS PrincipleGirName,
			dbo.GccsPrincipleTemplates.GccsDescription AS PrincipleGccsDescription, 
			dbo.GccsPrincipleTemplates.GirDescription AS PrincipleGirDescription,
			dbo.GccsPillarTemplates.Id AS PillarId, 
			dbo.GccsPillarTemplates.Name AS PillarName,
			dbo.fnGetPrincipleIssue(Principles.Id) AS Issue,		 
			Principles.Assessment AS PrincipleAssessment, 
            Principles.AssessmentEffectiveSince AS PrincipleAssessmentEffectiveSince, 
			Principles.AssessmentSummaryAndConclusionIntroSentence,
			Principles.Id AS PrincipleId,
			NULL AS AssessmentComment1,
			NULL AS AssessmentComment2,
			NULL AS AssessmentComment3,
			NULL AS AssessmentComment4,
			NULL AS AssessmentComment5,
			NULL AS AssessmentComment6
 FROM     dbo.GccsPrinciples AS Principles INNER JOIN
          dbo.GccsPillars ON Principles.PillarId = dbo.GccsPillars.Id INNER JOIN
          dbo.GccsCompanyProfiles ON dbo.GccsPillars.CompanyProfilerId = dbo.GccsCompanyProfiles.Id INNER JOIN
          dbo.GccsPrincipleTemplates ON Principles.TemplateId = dbo.GccsPrincipleTemplates.Id INNER JOIN
          dbo.GccsPillarTemplates ON dbo.GccsPillars.TemplateId = dbo.GccsPillarTemplates.Id AND dbo.GccsPrincipleTemplates.PillarTemplateId = dbo.GccsPillarTemplates.Id
	WHERE (dbo.GccsCompanyProfiles.OverallCompanyAssessment = 1 OR dbo.GccsCompanyProfiles.OverallCompanyAssessment = 2) -- 1 = Watchlist or 2 = NonCompliant
	   AND (dbo.GccsCompanyProfiles.GccsWorkflowStatus = 3) -- Final
	   AND (Principles.Assessment = 1 OR Principles.Assessment = 2)
	

	DECLARE @ID int
	DECLARE @Count int

	SELECT @ID = MIN(ID) FROM @Principles
	SELECT @Count = MAX(ID) FROM @Principles

	DECLARE @PrincipleId UNIQUEIDENTIFIER
	DECLARE @PrincipleAssessmentComments TABLE
	(
		PrincipleId UNIQUEIDENTIFIER NOT NULL,
		AssessmentComment1 NVARCHAR(1200) NULL,
		AssessmentComment2 NVARCHAR(1200) NULL,
		AssessmentComment3 NVARCHAR(1200) NULL,
		AssessmentComment4 NVARCHAR(1200) NULL,
		AssessmentComment5 NVARCHAR(1200) NULL,
		AssessmentComment6 NVARCHAR(1200) NULL
	)

	WHILE @ID <= @Count BEGIN

		SELECT @PrincipleId = PrincipleId
		FROM @Principles
		WHERE ID = @ID

		INSERT INTO @PrincipleAssessmentComments
		SELECT * FROM dbo.fnGetPrincipleAssessmentComments(@PrincipleId)

		SET @ID = @ID + 1
	END

	UPDATE @Principles
		SET AssessmentComment1 = AssessmentComments.AssessmentComment1,
			AssessmentComment2 = AssessmentComments.AssessmentComment2,
			AssessmentComment3 = AssessmentComments.AssessmentComment3,
			AssessmentComment4 = AssessmentComments.AssessmentComment4,
			AssessmentComment5 = AssessmentComments.AssessmentComment5,
			AssessmentComment6 = AssessmentComments.AssessmentComment6
	FROM @Principles AS Principles 
	INNER JOIN @PrincipleAssessmentComments AS AssessmentComments ON Principles.PrincipleId = AssessmentComments.PrincipleId

	SELECT CompanyId, PrincipleTemplateId, PrincipleGccsName, PrincipleGirName, PrincipleGccsDescription, PrincipleGirDescription, PillarId, PillarName, Issue, PrincipleAssessment, PrincipleAssessmentEffectiveSince, AssessmentSummaryAndConclusionIntroductionSentence, AssessmentComment1, AssessmentComment2, AssessmentComment3, AssessmentComment4, AssessmentComment5, AssessmentComment6
	FROM @Principles
RETURN 0

GO


